﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataTier
{
    [Serializable]
    public class Areal_Data
    {
        private Areal_Data()
        {

        }

        #region Properties
        private int klagenr;

        public int Klagenr
        {
            get { return klagenr; }
            set { klagenr = value; }
        }
        private string bbr_nr;

        public string Bbr_nr
        {
            get { return bbr_nr; }
            set { bbr_nr = value; }
        }
        private string arealtype;

        public string ArealType
        {
            get { return arealtype; }
            set { arealtype = value; }
        }
        private double areal;

        public double Areal
        {
            get { return areal; }
            set { areal = value; }
        }
        private double arealPris;

        public double ArealPris
        {
            get { return arealPris; }
            set { arealPris = value; }
        }

        #endregion

        public static List<Areal_Data> Load(string connStr, int klageNr)
        {
            var toReturn = new List<Areal_Data>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlStr = "select * from areal_data where klagenr = " + klageNr + " order by bbr_nr";
                    var sqlCommand = new SqlCommand(sqlStr, conn);
                    var sqlReader = sqlCommand.ExecuteReader();


                    while (sqlReader.Read())
                    {
                        toReturn.Add(new Areal_Data
                        {
                            klagenr = int.Parse(sqlReader.GetInt16(0).ToString()),
                            bbr_nr = sqlReader.GetString(1),
                            arealtype = sqlReader.GetString(2),
                            areal = sqlReader.GetDouble(3),
                            arealPris = sqlReader.GetDouble(4)
                        });
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;
        }

        public static Areal_Data Insert(string connStr, int klageNr, string bbr_nr, string arealType, double areal, double arealPris)
        {
            var toReturn = new Areal_Data
            {
                klagenr = klageNr,
                bbr_nr = bbr_nr,
                arealtype = arealType,
                areal = areal,
                arealPris = arealPris
            };
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("insert into areal_data (klagenr, bbr_nr, arealtype, areal, arealpris) values (@klagenr, @bbr_nr, @arealtype, @areal, @arealpris)", conn);

                    sqlCommand.Parameters.Add("@klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@bbr_nr", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@arealtype", SqlDbType.VarChar, 205);
                    sqlCommand.Parameters.Add("@areal", SqlDbType.Float);
                    sqlCommand.Parameters.Add("@arealpris", SqlDbType.Float);
                    /*
                    klagenr	smallint
                    bbr_nr	varchar(50)
                    arealtype	varchar(205)
                    areal	float
                    arealpris	float
	
                     * */
                    sqlCommand.Parameters["@klagenr"].Value = toReturn.klagenr;
                    sqlCommand.Parameters["@bbr_nr"].Value = toReturn.bbr_nr;
                    sqlCommand.Parameters["@arealtype"].Value = toReturn.arealtype;
                    sqlCommand.Parameters["@areal"].Value = toReturn.areal;
                    sqlCommand.Parameters["@arealpris"].Value = toReturn.arealPris;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;
        }

        public void Save(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("update areal_data set arealtype = @arealtype, areal = @areal, arealpris = @arealpris where klagenr = @klagenr and bbr_nr = @bbr_nr", conn);

                    sqlCommand.Parameters.Add("@arealtype", SqlDbType.VarChar, 205);
                    sqlCommand.Parameters.Add("@areal", SqlDbType.Float);
                    sqlCommand.Parameters.Add("@arealpris", SqlDbType.Float);
                    sqlCommand.Parameters.Add("@klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@bbr_nr", SqlDbType.VarChar, 50);

                    /*
                    klagenr	smallint
                    bbr_nr	varchar(50)
                    arealtype	varchar(205)
                    areal	float
                    arealpris	float
	
                     * */
                    sqlCommand.Parameters["@arealtype"].Value = this.arealtype;
                    sqlCommand.Parameters["@areal"].Value = this.areal;
                    sqlCommand.Parameters["@arealpris"].Value = this.arealPris;
                    sqlCommand.Parameters["@klagenr"].Value = this.klagenr;
                    sqlCommand.Parameters["@bbr_nr"].Value = this.bbr_nr;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }

        }

        public void Delete(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("delete from areal_data where klagenr = @klagenr and bbr_nr = @bbr_nr", conn);

                    sqlCommand.Parameters.Add("@klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@bbr_nr", SqlDbType.VarChar, 50);

                    sqlCommand.Parameters["@klagenr"].Value = this.klagenr;
                    sqlCommand.Parameters["@bbr_nr"].Value = this.bbr_nr;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }

        }
    }
}
